-- @owner: @zhanghuan96
-- @date: 2024/11/12
-- @testpoint: 使用函数解析触发器，部分合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_utility;
create extension gms_utility;
--step1:创建表;expect:成功
drop table if exists tb_resolve0015;
CREATE TABLE tb_resolve0015 (
    id SERIAL PRIMARY KEY,
    data TEXT,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--step2:创建函数;expect:成功
drop function if exists func_resolve0015;
CREATE OR REPLACE FUNCTION func_resolve0015()
RETURNS TRIGGER AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;
/
--step3:创建触发器;expect:成功
drop trigger if exists before_insert_trigger on tb_resolve0015;
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON tb_resolve0015
FOR EACH ROW EXECUTE procedure func_resolve0015();
/
--step4:解析;expect:成功，part1_type为12
declare
    name varchar2 := 'public.before_insert_trigger';
    context number := 3;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step5:不带schema;expect:成功
declare
    name varchar2 := 'before_insert_trigger';
    context number := 3;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step6:创建触发器的同义词;expect:成功
drop synonym if exists syn_tg;
CREATE OR REPLACE SYNONYM public.syn_tg FOR before_insert_trigger;
--step7:解析;expect:报错，对象不存在
declare
    name varchar2 := 'public.syn_tg';
    context number := 3;
    schema  varchar2;
    part1   varchar2;
    part2   varchar2;
    dblink  varchar2;
    part1_type  number;
    object_number   number;
begin
    gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
    raise info 'INFO:schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %', schema, part1, part2, dblink, part1_type, object_number;
end;
/
--step8:清理环境;expect:成功
drop table if exists tb_resolve0015;
drop function if exists func_resolve0015;
drop synonym if exists syn_tg;
drop extension if exists gms_utility;